from sqlite3 import IntegrityError
import json
from django.http import JsonResponse
from django.db.models import F
from db_man_app.models import Score, Student, Course


def get_all(request):
    # 获取所有成绩记录及其关联的学生和课程信息
    scores = Score.objects.select_related(
        'student',
        'course'
    ).annotate(
        name=F('student__name'),
        course_name=F('course__course_name')
    ).all().order_by('student_id')

    # 构建返回数据
    tableData = []
    for score in scores:
        tableData.append({
            'student_id': score.student_id,
            'name': score.name,  # 虽然前端没显示，但select中使用了
            'course_name': score.course_name,
            'usual_score': float(score.usual_score) if score.usual_score else 0.0,
            'final_score': float(score.final_score) if score.final_score else 0.0,
            # 可以添加计算总评成绩
            'total_score': float(score.usual_score or 0) * 0.3 + float(score.final_score or 0) * 0.7
        })

    return JsonResponse({
        'tableData': tableData,
        'status': 'success',
        'message': '成绩数据获取成功'
    })


#获取课程列表
def get_course_form(request):
    try:
        data=Course.objects.all().values('course_id','course_code','course_name')
        return JsonResponse({
            'status': 'success',
            'data': list(data)
        },safe=False)
    except IntegrityError:
        return JsonResponse(0, safe=False)


#获取学生列表、
def get_students(request):
    try:
        data=Student.objects.all().values('student_id','name')
        return JsonResponse({
            'status': 'success',
            'data': list(data)
        })
    except IntegrityError:
        return JsonResponse(0, safe=False)


def add_one(request):
    data = json.loads(request.body)
    course_id = data.get('course_id')
    student_id = data.get('student_id')
    usual_score = data.get('usual_score')
    final_score = data.get('final_score')
    try:
        Score.objects.create(
            student_id=student_id,
            course_id=course_id,
            usual_score=usual_score,
            final_score=final_score
            )
        return JsonResponse(1, safe=False)
    except IntegrityError:
        return JsonResponse(0, safe=False)


def updata_one(request):
    data = json.loads(request.body)
    student_id = data['formData']['student_id']
    usual_score = data['formData']['usual_score']
    final_score = data['formData']['final_score']
    old_course_name = data['old_course_name']
    old_course_id = Course.objects.get(course_name=old_course_name).course_id
    try:
        Score.objects.filter(student_id=student_id, course_id=old_course_id).update(
            usual_score=usual_score,
            final_score=final_score
        )
        return JsonResponse(1, safe=False)
    except IntegrityError:
        return JsonResponse(0, safe=False)

def del_one(request):
    data = json.loads(request.body)
    student_id = data['del_student_id']
    course_name=data['del_course_name']
    try:
        #先找对应课程id
        course_id=Course.objects.get(course_name=course_name).course_id
        Score.objects.filter(student_id=student_id, course_id=course_id).delete()
        return JsonResponse(1, safe=False)
    except IntegrityError:
        return JsonResponse(0, safe=False)
